package com.xxxy.ljh.myapplication.DaoImpl;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.xxxy.ljh.myapplication.Dao.MemberApplicationDao;
import com.xxxy.ljh.myapplication.dphelper.sqliteDBHelper;
import com.xxxy.ljh.myapplication.model.MemberApplication;

import java.util.ArrayList;
import java.util.List;

/**
 * 社团成员申请数据访问接口实现类。
 * 提供社团成员申请相关的数据库操作方法，包括提交申请、查询申请列表、审批申请等。
 */
public class MemberApplicationDaoImpl implements MemberApplicationDao {
    private sqliteDBHelper dbHelper;

    /**
     * 构造函数，初始化数据库帮助类。
     *
     * @param context 上下文对象，用于创建数据库连接
     */
    public MemberApplicationDaoImpl(Context context) {
        dbHelper = new sqliteDBHelper(context);
    }

    /**
     * 根据社团ID获取该社团所有待审核的成员申请记录。
     *
     * @param clubId 社团ID
     * @return 待审核的成员申请列表
     */
    @Override
    public List<MemberApplication> getPendingApplicationsByClubId(int clubId) {
        List<MemberApplication> applications = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = null;

        try {
            // 查询语句：关联用户表和社团表，筛选指定社团且状态为待审核（status=0）的申请，并按申请时间升序排列
            String sql = "SELECT am.*, u.real_name as user_name, a.association_name " +
                    "FROM association_member am " +
                    "LEFT JOIN t_user u ON am.user_id = u.user_id " +
                    "LEFT JOIN association a ON am.association_id = a.association_id " +
                    "WHERE am.association_id = ? AND am.status = 0 " +
                    "ORDER BY am.apply_time ASC";

            cursor = db.rawQuery(sql, new String[]{String.valueOf(clubId)});

            if (cursor != null && cursor.moveToFirst()) {
                do {
                    MemberApplication application = cursorToApplication(cursor);
                    applications.add(application);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }

        return applications;
    }

    /**
     * 根据用户ID获取该用户的所有社团申请记录。
     *
     * @param userId 用户ID
     * @return 该用户的申请记录列表
     */
    @Override
    public List<MemberApplication> getApplicationsByUserId(int userId) {
        List<MemberApplication> applications = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = null;

        try {
            // 查询语句：关联用户表和社团表，筛选指定用户的申请，并按申请时间降序排列
            String sql = "SELECT am.*, u.real_name as user_name, a.association_name " +
                    "FROM association_member am " +
                    "LEFT JOIN t_user u ON am.user_id = u.user_id " +
                    "LEFT JOIN association a ON am.association_id = a.association_id " +
                    "WHERE am.user_id = ? " +
                    "ORDER BY am.apply_time DESC";

            cursor = db.rawQuery(sql, new String[]{String.valueOf(userId)});

            if (cursor != null && cursor.moveToFirst()) {
                do {
                    MemberApplication application = cursorToApplication(cursor);
                    applications.add(application);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }

        return applications;
    }

    /**
     * 提交一个新的社团成员申请。
     *
     * @param application 成员申请信息对象
     * @return 是否插入成功
     */
    @Override
    public boolean submitApplication(MemberApplication application) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        long result;

        try {
            // 检查是否已经申请过
            String checkSql = "SELECT status FROM association_member WHERE user_id = ? AND association_id = ?";
            Cursor cursor = db.rawQuery(checkSql, new String[]{
                String.valueOf(application.getUserId()), 
                String.valueOf(application.getAssociationId())
            });

            if (cursor != null && cursor.moveToFirst()) {
                int status = cursor.getInt(0);
                cursor.close();
                
                // 如果已经是成员（状态为1）则不允许重复申请
                if (status == 1) {
                    return false;
                }
                
                // 如果之前申请被拒绝（状态为2）或者正在审核中（状态为0），则更新状态
                ContentValues values = new ContentValues();
                values.put("status", 0); // 重新设置为待审核状态
                values.put("apply_time", System.currentTimeMillis());
                
                result = db.update("association_member", values, 
                    "user_id = ? AND association_id = ?", 
                    new String[]{
                        String.valueOf(application.getUserId()), 
                        String.valueOf(application.getAssociationId())
                    });
            } else {
                // 插入新申请
                ContentValues values = new ContentValues();
                values.put("association_id", application.getAssociationId());
                values.put("user_id", application.getUserId());
                values.put("status", 0); // 默认待审核状态
                values.put("apply_time", System.currentTimeMillis());

                result = db.insert("association_member", null, values);
            }
        } catch (Exception e) {
            e.printStackTrace();
            result = -1;
        } finally {
            db.close();
        }

        return result > 0;
    }

    /**
     * 审批一个社团成员申请。
     *
     * @param memberId   申请记录ID
     * @param status     审核结果状态码（如1表示通过）
     * @param approverId 审核人ID
     * @param remark     审核备注信息
     * @return 是否更新成功
     */
    @Override
    public boolean approveApplication(int memberId, int status, int approverId, String remark) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int result;

        try {
            ContentValues values = new ContentValues();
            values.put("status", status);
            values.put("approver_id", approverId);
            values.put("approve_time", System.currentTimeMillis());
            values.put("approve_remark", remark);

            // 如果是通过申请，需要同步更新社团成员数量
            if (status == 1) {
                // 获取当前社团成员数并加一
                Cursor cursor = db.query("association", new String[]{"member_count"},
                        "association_id = (SELECT association_id FROM association_member WHERE member_id = ?)",
                        new String[]{String.valueOf(memberId)}, null, null, null);

                if (cursor != null && cursor.moveToFirst()) {
                    int currentCount = cursor.getInt(0);
                    ContentValues clubValues = new ContentValues();
                    clubValues.put("member_count", currentCount + 1);
                    db.update("association", clubValues,
                            "association_id = (SELECT association_id FROM association_member WHERE member_id = ?)",
                            new String[]{String.valueOf(memberId)});
                }

                if (cursor != null) {
                    cursor.close();
                }
            }

            result = db.update("association_member", values, "member_id = ?",
                    new String[]{String.valueOf(memberId)});
        } catch (Exception e) {
            e.printStackTrace();
            result = -1;
        } finally {
            db.close();
        }

        return result > 0;
    }

    /**
     * 根据申请ID获取具体的申请详情。
     *
     * @param memberId 申请记录ID
     * @return 对应的申请信息对象，若不存在则返回null
     */
    @Override
    public MemberApplication getApplicationById(int memberId) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = null;
        MemberApplication application = null;

        try {
            // 查询语句：关联用户表和社团表，获取完整的申请信息
            String sql = "SELECT am.*, u.real_name as user_name, a.association_name " +
                    "FROM association_member am " +
                    "LEFT JOIN t_user u ON am.user_id = u.user_id " +
                    "LEFT JOIN association a ON am.association_id = a.association_id " +
                    "WHERE am.member_id = ?";

            cursor = db.rawQuery(sql, new String[]{String.valueOf(memberId)});

            if (cursor != null && cursor.moveToFirst()) {
                application = cursorToApplication(cursor);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }

        return application;
    }

    /**
     * 将游标中的数据转换为MemberApplication实体对象。
     *
     * @param cursor 数据库查询结果游标
     * @return 转换后的MemberApplication对象
     */
    private MemberApplication cursorToApplication(Cursor cursor) {
        MemberApplication application = new MemberApplication();
        application.setMemberId(cursor.getInt(cursor.getColumnIndexOrThrow("member_id")));
        application.setAssociationId(cursor.getInt(cursor.getColumnIndexOrThrow("association_id")));
        application.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow("user_id")));
        application.setStatus(cursor.getInt(cursor.getColumnIndexOrThrow("status")));
        
        int applyTimeIndex = cursor.getColumnIndex("apply_time");
        if (applyTimeIndex != -1 && !cursor.isNull(applyTimeIndex)) {
            application.setApplyTime(cursor.getString(applyTimeIndex));
        }

        int approveTimeIndex = cursor.getColumnIndex("approve_time");
        if (approveTimeIndex != -1 && !cursor.isNull(approveTimeIndex)) {
            application.setApproveTime(cursor.getString(approveTimeIndex));
        }

        int approverIdIndex = cursor.getColumnIndex("approver_id");
        if (approverIdIndex != -1 && !cursor.isNull(approverIdIndex)) {
            application.setApproverId(cursor.getInt(approverIdIndex));
        }

        int remarkIndex = cursor.getColumnIndex("approve_remark");
        if (remarkIndex != -1 && !cursor.isNull(remarkIndex)) {
            application.setApproveRemark(cursor.getString(remarkIndex));
        }

        int userNameIndex = cursor.getColumnIndex("user_name");
        if (userNameIndex != -1 && !cursor.isNull(userNameIndex)) {
            application.setUserName(cursor.getString(userNameIndex));
        }

        int assocNameIndex = cursor.getColumnIndex("association_name");
        if (assocNameIndex != -1 && !cursor.isNull(assocNameIndex)) {
            application.setAssociationName(cursor.getString(assocNameIndex));
        }

        return application;
    }
}